package com.cogent.dao.facade.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.cogent.common.exception.SystemRuntimeException;
import com.cogent.dao.domain.Pagination;
import com.cogent.dao.domain.ParamDomain;
import com.cogent.dao.domain.QueryParamDomain;
import com.cogent.dao.domain.SqlDomain;
import com.cogent.dao.domain.TableInfoDomain;
import com.cogent.dao.facade.BussinessDaoFacade;
import com.cogent.dao.orm.DBOperator;
import com.cogent.dao.sql.ISqlBuilder;

public class BussinessDaoFacadeImpl implements BussinessDaoFacade {
	private boolean IS_TRAN = false;
	private boolean IS_CONN = false;
	private boolean IS_INEROPEN = false;
	private DBOperator dbOperator = null;
	private ISqlBuilder sqlBuilder = null;
	
	public String save(String tableName, HashMap valMap) {
		String state = "0";
		SqlDomain sqldomain = sqlBuilder.insertSql(tableName, valMap);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			if(!IS_TRAN)
				dbOperator.beginTran();
			dbOperator.executeSql(sqldomain.getSql(), sqldomain.getParam());
			Iterator it = sqldomain.getSubSqlList().iterator();
			while(it.hasNext()) {
				SqlDomain subSqlDomain = (SqlDomain)it.next();
				dbOperator.executeSql(subSqlDomain.getSql(), subSqlDomain.getParam());
			}
			if(!IS_TRAN)
				dbOperator.commitTran();
			state = sqldomain.getId();
		}catch(SystemRuntimeException se) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, se, "err.msg.system");
		}catch(Throwable t) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, t, "err.msg.system");
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}		
		
		return state;
	}
	
	public String batchSave(String tableName, List valList) {
		String state = "0";		
		if(connIsNULL())
			dbOperator.openDB();
		try{
			if(!IS_TRAN)
				dbOperator.beginTran();
			Iterator firstit = valList.iterator();
			while(firstit.hasNext()) {
				HashMap valMap = (HashMap)firstit.next();
				SqlDomain sqldomain = sqlBuilder.insertSql(tableName, valMap);
				dbOperator.executeSql(sqldomain.getSql(), sqldomain.getParam());
				Iterator it = sqldomain.getSubSqlList().iterator();
				while(it.hasNext()) {
					SqlDomain subSqlDomain = (SqlDomain)it.next();
					dbOperator.executeSql(subSqlDomain.getSql(), subSqlDomain.getParam());
				}
			}
			if(!IS_TRAN)
				dbOperator.commitTran();
		}catch(SystemRuntimeException se) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, se, "err.msg.system");
		}catch(Throwable t) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, t, "err.msg.system");
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}		
		
		return state;
	}
	
	public String update(String tableName, HashMap valMap) {
		String state = "0";
		SqlDomain sqldomain = sqlBuilder.updateSql(tableName, valMap);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			if(!IS_TRAN)
				dbOperator.beginTran();
			dbOperator.executeSql(sqldomain.getSql(), sqldomain.getParam());
			Iterator it = sqldomain.getSubSqlList().iterator();
			while(it.hasNext()) {
				SqlDomain subSqlDomain = (SqlDomain)it.next();
				dbOperator.executeSql(subSqlDomain.getSql(), subSqlDomain.getParam());
			}
			if(!IS_TRAN)
				dbOperator.commitTran();
			state = sqldomain.getId();
		}catch(SystemRuntimeException se) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, se, "err.msg.system");
		}catch(Throwable t) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, t, "err.msg.system");
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return state;
	}
	
	public String update(String tableName, HashMap valMap, String idkey) {
		String state = "0";
		SqlDomain sqldomain = sqlBuilder.updateSql(tableName, valMap, idkey);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			if(!IS_TRAN)
				dbOperator.beginTran();
			dbOperator.executeSql(sqldomain.getSql(), sqldomain.getParam());
			Iterator it = sqldomain.getSubSqlList().iterator();
			while(it.hasNext()) {
				SqlDomain subSqlDomain = (SqlDomain)it.next();
				dbOperator.executeSql(subSqlDomain.getSql(), subSqlDomain.getParam());
			}
			if(!IS_TRAN)
				dbOperator.commitTran();
			state = sqldomain.getId();
		}catch(SystemRuntimeException se) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, se, "err.msg.system");
		}catch(Throwable t) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, t, "err.msg.system");
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return state;
	}
	
	public String saveOrUpdate(String tableName, HashMap valMap) {
		String state = "0";
		SqlDomain sqldomain = sqlBuilder.insertOrUpdateSql(tableName, valMap);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			if(!IS_TRAN)
				dbOperator.beginTran();
			dbOperator.executeSql(sqldomain.getSql(), sqldomain.getParam());
			Iterator it = sqldomain.getSubSqlList().iterator();
			while(it.hasNext()) {
				SqlDomain subSqlDomain = (SqlDomain)it.next();
				dbOperator.executeSql(subSqlDomain.getSql(), subSqlDomain.getParam());
			}
			if(!IS_TRAN)
				dbOperator.commitTran();
			state = sqldomain.getId();
		}catch(SystemRuntimeException se) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, se, "err.msg.system");
		}catch(Throwable t) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, t, "err.msg.system");
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return state;
	}
	
	public String delete(String tableName, List<QueryParamDomain> valList) {
		String state = "0";
		SqlDomain sqldomain = sqlBuilder.deleteSql(tableName, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			if(!IS_TRAN)
				dbOperator.beginTran();
			dbOperator.executeSql(sqldomain.getSql(), sqldomain.getParam());
			Iterator it = sqldomain.getSubSqlList().iterator();
			while(it.hasNext()) {
				SqlDomain subSqlDomain = (SqlDomain)it.next();
				dbOperator.executeSql(subSqlDomain.getSql(), subSqlDomain.getParam());
			}
			if(!IS_TRAN)
				dbOperator.commitTran();
		}catch(SystemRuntimeException se) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw se;
		}catch(Throwable t) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, t, "err.msg.system");
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return state;
	}
	
	public String executeSql(String sql) {
		String state = "0";
		if(connIsNULL())
			dbOperator.openDB();
		try{
			if(!IS_TRAN)
				dbOperator.beginTran();
			dbOperator.executeSql(sql);
			if(!IS_TRAN)
				dbOperator.commitTran();
		}catch(SystemRuntimeException se) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw se;
		}catch(Throwable t) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, t, "err.msg.system");
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}		
		
		return state;
	}
	
	public String executeSql(String sql, List<QueryParamDomain> paramList) {
		String state = "0";
		if(connIsNULL())
			dbOperator.openDB();
		try{
			if(!IS_TRAN)
				dbOperator.beginTran();
			dbOperator.executeSql(sql, sqlBuilder.getParamList(paramList));
			if(!IS_TRAN)
				dbOperator.commitTran();
		}catch(SystemRuntimeException se) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw se;
		}catch(Throwable t) {
			if(!IS_TRAN)
				dbOperator.rollbackTran();
			state = "-1";
			throw new SystemRuntimeException(SystemRuntimeException.HIGH, t, "err.msg.system");
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return state;
	}
	
	public HashMap getByID(String tableName,  List<QueryParamDomain> valList) {
		HashMap retMap = new HashMap();
		SqlDomain sqldomain = sqlBuilder.querySql(tableName, valList);
		
		if(connIsNULL())
			dbOperator.openDB();
		try{
			retMap = dbOperator.getByID(sqldomain.getSql(), sqldomain.getParam());
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retMap;
	}
	
	public HashMap getOneRow(String sql) {
		HashMap retMap = new HashMap();
		if(connIsNULL())
			dbOperator.openDB();
		try{
			retMap = dbOperator.getOneRow(sql);
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retMap;
	}
	
	public HashMap getOneRow(String sql, List<QueryParamDomain> valList) {
		HashMap retMap = new HashMap();
		SqlDomain sqldomain = sqlBuilder.queryWithPrefixSql(sql, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			List list = dbOperator.query(sqldomain.getSql(), sqldomain.getParam());
			if(list.size() > 0)
				retMap = (HashMap)list.get(0);
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retMap;
	}
	
	public HashMap getOneRowByTable(String tableName,  List<QueryParamDomain> valList) {
		HashMap retMap = new HashMap();
		SqlDomain sqldomain = sqlBuilder.querySql(tableName, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			List list = dbOperator.query(sqldomain.getSql(), sqldomain.getParam());
			if(list.size() > 0)
				retMap = (HashMap)list.get(0);
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retMap;
	}
	
	public List query(String tableName,  List<QueryParamDomain> valList) {
		List retList = new ArrayList();
		SqlDomain sqldomain = sqlBuilder.querySql(tableName, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			retList = dbOperator.query(sqldomain.getSql(), sqldomain.getParam());
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retList;
	}
	
	public List queryWithPostfix(String tableName,  List<QueryParamDomain> valList, String postfix) {
		List retList = new ArrayList();
		SqlDomain sqldomain = sqlBuilder.querySql(tableName, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			retList = dbOperator.query(sqldomain.getSql() + " " + postfix, sqldomain.getParam());
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retList;
	}
	
	public List queryWihtParam(String sql, List<QueryParamDomain> paramList) {
		List retList = new ArrayList();
		if(connIsNULL())
			dbOperator.openDB();
		try{
			retList = dbOperator.query(sql, sqlBuilder.getParamList(paramList));
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retList;
	}
	
	public List query(String sql,  List<QueryParamDomain> valList, String groupBy) {
		List retList = new ArrayList();
		SqlDomain sqldomain = sqlBuilder.queryWithPrefixSql(sql, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			retList = dbOperator.query(sqldomain.getSql() + " " + groupBy, sqldomain.getParam());
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retList;
	}
	
	public List queryLimit(String tableName,  List<QueryParamDomain> valList, int limit) {
		return queryLimit(tableName, valList, "", limit);
	}
	
	public List queryLimit(String tableName,  List<QueryParamDomain> valList, String order, int limit) {
		List retList = new ArrayList();
		SqlDomain sqldomain = sqlBuilder.querySql(tableName, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			String cursql = sqldomain.getSql().toLowerCase();
			if(cursql.indexOf(" where ") < 0)
				cursql += " where rownum<=" + limit;
			else
				cursql += " and rownum<=" + limit;
			retList = dbOperator.query(cursql + " " + order, sqldomain.getParam());
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retList;
	}
	
	public List queryLimitBySql(String sql, List<QueryParamDomain> valList, int limit) {
		return queryLimitBySql(sql, valList, "", limit);
	}
	
	public List queryLimitBySql(String sql,  List<QueryParamDomain> valList, String order, int limit) {
		List retList = new ArrayList();
		SqlDomain sqldomain = sqlBuilder.queryWithPrefixSql(sql, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			String cursql = sqldomain.getSql().toLowerCase();
			if(cursql.indexOf(" where ") < 0)
				cursql += " where rownum<=" + limit;
			else
				cursql += " and rownum<=" + limit;
			retList = dbOperator.query(cursql + " " + order, sqldomain.getParam());
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retList;
	}
	
	public Pagination queryPaged(String tableName, List<QueryParamDomain> valList, String order, int page, int pageSize) {
		Pagination pagination = new Pagination();
		SqlDomain sqldomain = sqlBuilder.querySql(tableName, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			pagination = dbOperator.queryPagination(sqldomain.getSql(), sqldomain.getParam(), order, page, pageSize);
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return pagination;
	}
	
	public List queryWithPrefix(String prefixSql, List<QueryParamDomain> valList) {
		List retList = new ArrayList();
		SqlDomain sqldomain = sqlBuilder.queryWithPrefixSql(prefixSql, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			retList = dbOperator.query(sqldomain.getSql(), sqldomain.getParam());
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retList;
	}
	
	public Pagination queryWithPrefixPaged(String prefixSql, List<QueryParamDomain> valList, String order, int page, int pageSize) {
		Pagination pagination = new Pagination();
		SqlDomain sqldomain = sqlBuilder.queryWithPrefixSql(prefixSql, valList);
		if(connIsNULL())
			dbOperator.openDB();
		try{
			pagination = dbOperator.queryPagination(sqldomain.getSql(), sqldomain.getParam(), order, page, pageSize);
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return pagination;
	}
	
	public List query(String sql) {
		List retList = new ArrayList();
		if(connIsNULL())
			dbOperator.openDB();
		try{
			retList = dbOperator.query(sql);
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retList;
	}
	
	public Pagination queryPaged(String sql, String order, int page, int pageSize) {
		Pagination pagination = new Pagination();
		if(connIsNULL())
			dbOperator.openDB();
		try{
			pagination = dbOperator.queryPagination(sql, order, page, pageSize);
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return pagination;
	}
	
	public TableInfoDomain getColumnInfo(String tableName) {
		return dbOperator.getColumnInfo(tableName);
	}
	
	public Map getDBFieldMappedType() {
		return dbOperator.getDBFieldMappedType();
	}
	
	public void beginTran() {
		IS_TRAN = true;
		if(!IS_CONN) {
			dbOperator.openDB();
			IS_INEROPEN = true;
		}
		dbOperator.beginTran();
	}
	
	public void commitTran() {
		IS_TRAN = false;
		dbOperator.commitTran();
		if(!IS_CONN) {
			dbOperator.closeDB();
			IS_INEROPEN = false;
		}
	}
	
	public void rollbackTran() {
		IS_TRAN = false;
		dbOperator.rollbackTran();
		if(!IS_CONN) {
			dbOperator.closeDB();
			IS_INEROPEN = false;
		}
	}
	
	public void createConn() {
		dbOperator.openDB();
		IS_CONN = true;
	}
	
	public void closeConn() {
		dbOperator.closeDB();
		IS_CONN = false;
	}
	
	private boolean isCloseDb() {
		return (!IS_CONN) && (!IS_INEROPEN);
	}
	
	private boolean connIsNULL() {
		return (!IS_CONN) && (!IS_INEROPEN);
	}

	public DBOperator getDbOperator() {
		return dbOperator;
	}

	public void setDbOperator(DBOperator dbOperator) {
		this.dbOperator = dbOperator;
	}

	public ISqlBuilder getSqlBuilder() {
		return sqlBuilder;
	}

	public void setSqlBuilder(ISqlBuilder sqlBuilder) {
		this.sqlBuilder = sqlBuilder;
	}
	
	public String getDataSourceUserName(){
		return this.dbOperator.getUserName();
	}
	
	public String getTableName(String sql) {
		return this.dbOperator.getTableName(sql).toLowerCase();
	}
	
	public ISqlBuilder getORMSqlBuilder() {
		return this.sqlBuilder;
	}
	
	public List queryWhithParam(String sql,  List<ParamDomain> paramlist, String groupBy) {
		List retList = new ArrayList();
		if(connIsNULL())
			dbOperator.openDB();
		try{
			retList = dbOperator.query(sql + " " + groupBy, paramlist);
		}finally{
			if(isCloseDb())
				dbOperator.closeDB();
		}
		
		return retList;
	}
}
